const Service = require('egg').Service;

class GradeService extends Service {

  async addGrade() {
    const { app } = this;
    const { stuid, theory, skill, username, createTime } = this.ctx.request.body;
    const data = await app.mysql.get('users', { stuid })
    if (data) {
      const data = await app.mysql.insert('grade', {
        stuid, theory, skill, username, createTime
      })
      return { code: 0, msg: 'success', data }
    } else {
      return { code: -1, msg: 'fail' }
    }
  }

  async updateGrade() {
    const { id, theory, skill } = this.ctx.request.body;
    console.log(this.ctx.request.body, 'body')
    const data = await app.mysql.get('users', { stuid })
    const list = await this.app.mysql.query(`
    update grade set theory=?, skill=? where id=?
   `, [id, theory, skill])
    return { code: 0, msg: 'success', data: list }
    // if (data) {
    //   // const data = await app.mysql.update('grade', {
    //   //   id, theory, skill
    //   // })
    //   const data = await this.app.mysql.query(`
    //    update grade set theory=?, skill=? where id=?
    //   `, [id, theory, skill])
    //   return { code: 0, msg: 'success', data }
    // } else {
    //   return { code: -1, msg: 'fail' }
    // }
  }

  async grade() {
    const { app } = this;
    const data = await app.mysql.query(`
     select users.username, theory, skill from users inner join grade on users.stuid = grade.stuid 
    `)
    return { code: 0, msg: 'success', data }
  }

  // 删除成绩接口
  async deleteGrade() {
    const { app } = this;
    const { id } = this.ctx.params;
    const data = await app.mysql.delete('grade', { id })
    return { code: 0, msg: 'success', data }
  }

  // 搜索接口 username  技能/理论
  async searchGrade() {
    const { app, ctx } = this;
    const { username, start, end } = ctx.query;
    const results = await this.app.mysql.query(`
     select * from grade where username='${username}' and theory > 60 and skill > 60;
    `)
    console.log('sql: ---->', `select * from grade where username='${username}' and createTime between '${start}' and '${end}'`)
    // console.log(results, 'results')
    return { code: 0, msg: 'success', data: results }
    // const results = await this.app.mysql.select('posts', { // 搜索 post 表
    //   where: { username, theory, skill }, // WHERE 条件
    //   columns: ['username', "theory", "skill"],  // 要查询的表字段
    //   // orders: [['created_at', 'desc'], ['id', 'desc']], // 排序方式
    //   // limit: size * 1, // 返回数据量
    //   // offset: page * size, // 数据偏移量
    // });
  }

  async pageGrade() {
    const { app, ctx } = this;
    const { page, size } = ctx.query;
    console.log('我是分页代码')
    const total = await this.app.mysql.select('grade')
    const results = await this.app.mysql.select('grade', { // 搜索 post 表
      limit: size * 1, // 返回数据量
      offset: page * size, // 数据偏移量
    });
    return { code: 0, msg: 'success', data: results, total: total.length }
  }

  async betchDelete() {
    const { ids } = this.ctx.params; // 1,2,3,4
    const data = await this.app.mysql.query(`
     delete from grade where id in ('${ids}') 
    `)
    return { code: 0, msg: 'success', data }
  }

  async gradeAll() {
    const { app, ctx } = this;
    // 前端 做下拉框 及格 不及格
    const { flag } = ctx.query;
    let sql = '';
    if (flag === 'true') {
      sql = `select * from grade where theory > 60 and skill > 60`
    } else if (flag === 'false') {
      sql = `select * from grade where theory < 60 or skill < 60`
    }
    console.log(sql, 'sql----------sql')
    const data = await app.mysql.query(sql)
    return { code: 0, msg: 'success', data }
  }



  // async deleteGrade() {
  //   const { app } = this;

  // }
}

module.exports = GradeService;